if exists (select 1
          from sysobjects
          where  id = object_id('bd_fn_deptAgTree')
          and type in ('IF', 'FN', 'TF'))
   drop function bd_fn_deptAgTree
go


create function bd_fn_deptAgTree (@deptId BIGINT)
RETURNS VARCHAR(MAX)
BEGIN
    DECLARE @result VARCHAR(MAX);

    WITH child(dep_serial, dep_parent, dep_no, dep_name, did) 
    AS 
    (
        SELECT dep_serial, dep_parent, dep_no, dep_name, @deptId dId  FROM dt_dep 
        WHERE dep_serial = @deptId   
     
        union all
     
        SELECT a.dep_serial, a.dep_parent, a.dep_no, a.dep_name, b.did
        FROM dt_dep a 
        INNER JOIN  
        child b          
        ON a.dep_serial=b.dep_parent   
    ),
    gp AS (            
      SELECT b.dep_serial,
        STUFF(
           ( 
            SELECT ','+ CAST(a.dep_name AS VARCHAR) FROM child a WHERE b.dep_serial = a.did ORDER BY a.dep_no FOR XML PATH('')
           ),1 ,1, '') treeNode
      FROM dt_dep  b 
      WHERE b.dep_serial=@deptId
      GROUP BY b.dep_serial
    )
    SELECT @result=treeNode FROM gp;

    RETURN @result
END
go
